// 打造一个可以连接数据库的方法
const mysql = require('mysql2/promise')
const {database} = require('../config/index.js')

// 创建连接池
const pool = mysql.createPool({
    host: database.HOST,
    user: database.USERNAME,
    password: database.PASSWORD,
    database: database.DATABASE,
    port:database.PORT
})

// 可以连接数据库的方法
const allServers = {
    query: async function(sql){
        try{
            // 通过连接池来创建连接
        const conn = await pool.getConnection()
        // 对该链接执行某些操作
        const [rows,err] = await conn.query(sql)
        // 释放连接
        pool.releaseConnection(conn)

        return Promise.resolve(rows)
        }catch (error){
            return Promise.reject(error)
        }
    }
}

const userLogin = (username,password)=>{
    const _sql = `select * from users where username="${username}" and password="${password}"`
    return allServers.query(_sql)
}

// 查找账号
const userFind = (username) =>{
    const _sql = `select * from users where username="${username}"`
    return allServers.query(_sql)
}

// 注册账号
const userRegister = (value) =>{
    const _sql = `insert into users (username,password,nickname) values ("${value.username}","${value.password}","${value.nickname}")`
    return allServers.query(_sql)
}

// 根据分类查找数据
const findNoteListByType = (note_type,id) =>{
    const _sql = `select * from note where note_type="${note_type}" and userId="${id}"`
    return allServers.query(_sql)
}

// 根据ID查找详情
const findDetail = (id) =>{
    const _sql = `select * from note where id="${id}"`
    return allServers.query(_sql)
}

// 发布笔记
const addNote = (values) =>{
    const _sql = `insert into note (title,note_type,note_content,head_img,c_time,m_time,userId,nickname) values 
    ("${values.title}", "${values.note_type}", "${values.note_content}", "${values.head_img}", "${values.c_time}", "${values.m_time}", "${values.userId}", "${values.nickname}")`
    return allServers.query(_sql)
}

module.exports = {
    userLogin,
    userFind,
    userRegister,
    findNoteListByType,
    findDetail,
    addNote
}
